The Ask

DCPO Halawa and Data Analyst Loftus asked if it would be possible to use Cook County Medical Examiner Office data, in addition to internal sources, to measure shooting incidents involving Department invovled youth. While that question was answered in another document, the full capability of these analytic tools could not be demonstrated due to security concerns regarding department data. This report is an attempt to more completely showcase the possibilities of GIS powered dashboards and reports.

The Lift

The requirements for this report have not changed significantly as the following steps are still required:

Importing the Data

Importing the medical examiner’s (ME) office data is simple: The Cook County Open Data website has a link that when incorporated into code of the analysis, allows for easy analysis. This data is updated daily, and because of the link, no file needs to downloaded then uploaded to the analytic code. In short, powering on the report loads the most current data into the analysis.

Cleaning Data

Even though this data is coming from directly from the ME office’s database, cleaning will still need to be done. The data is already fairly tidy, but they are organized by the rules establisehd by the ME, and contains variables that are not – as of this writing – of interest to the department:

Medical Examiner’s Office Data: 03/07/2019
age casenumber cold_related death_date gender heat_related incident_date latino manner objectid primarycause race primarycause_lineb secondarycause incident_street incident_city incident_zip opioids gunrelated primarycause_linec latitude location.type location.coordinates longitude residence_city residence_zip
52 ME2014-00288 NO 1409452140 Male NO 1409169600 FALSE NATURAL 55772 HYPERTENSIVE CARDIOVASCULAR DISEASE White NA NA NA NA NA NA NA NA NA NA NULL NA NA NA
40 ME2017-04206 NO 1504886460 Male NO 1504885200 TRUE NATURAL 55847 SUBARACHNOID HEMORRHAGE White HYPERTENSIVE CARDIOVASCULAR DISEASE OBESITY NA NA NA NA NA NA NA NA NULL NA NA NA
2 ME2015-03835 NO 1441429200 NA NO 1441489200 FALSE NA 56116 HUMAN REMAINS (SEE CASE 2015-3854), NO DEATH CERTIFICATE ISSUED Black NA NA NA NA NA NA NA NA NA NA NULL NA NA NA
46 ME2015-03052 NO 1437257700 Male NO 1435856400 FALSE NATURAL 56404 COMPLICATIONS OF LIVER CIRRHOSIS White NA NA NA NA NA NA NA NA NA NA NULL NA NA NA

The steps for this data set, then, include removing those variables that are of no interest and filtering results to focus on the age-range of interest to the department. Additionally, the way the ME classify’s race is complicated. Race is listed as White, Black, NA, Asian, Other, Unknown, Am. Indian. Latino is listed as a seperate factor. For the department’s needs, the ME’s data needs to add Latino to race. This is a simple cleaning function in R, that creates the follow categories: White, White Latino, Black, NA, Asian, Other, Black Latino, Unknown, Latino, Am. Indian, Unknown Latino, Asian Latino, NA Latino, Am. Indian Latino

Visualizing the Data

The cleaned ME dataset has following 19 columns: age, death_date, gender, incident_date, manner, primarycause, race, incident_street, incident_city, incident_zip, opioids, gunrelated, primarycause_linec, latitude, location.type, location.coordinates, longitude, residence_city, residence_zip. Displaying all of this data in a single column is not useful on paper, but because this report is web based, we can add scroll bars to the table in order to view all the entries.

Medical Examiner’s Office Data 03/07/2019
age death_date gender incident_date manner primarycause race incident_street incident_city incident_zip opioids gunrelated primarycause_linec latitude location.type location.coordinates longitude residence_city residence_zip
26 1504335780 Male 1504333200 HOMICIDE GUNSHOT WOUNDS OF TORSO Black 8353 S. HERMITAGE AVE CHICAGO 60620 NA TRUE NA 41.7414722 Point c(-87.6665973, 41.7414722) -87.6665973 Chicago 60620
25 1487155020 Male 1487151900 HOMICIDE MULTIPLE GUNSHOT WOUNDS White Latino 2390 N. Lake Shore Drive CHICAGO 60611 NA TRUE NA 41.9257631 Point c(-87.6313155, 41.9257631) -87.6313155 Chicago 60608
22 1505102580 Male 1505091780 HOMICIDE GUNSHOT WOUND OF HEAD Black 11101 S. Michigan CHICAGO 60628 NA TRUE NA 41.6935327 Point c(-87.5386313, 41.6935327) -87.5386313 Chicago 60628
15 1495488900 Male 1495295520 HOMICIDE GUNSHOT WOUND TO MOUTH White Latino 2106 DAVID DRIVE DES PLAINES 60018 NA TRUE NA 42.0061767 Point c(-87.8717746, 42.0061767) -87.8717746 Des Plaines 60018
25 1497773580 Female 1497771720 HOMICIDE GUNSHOT WOUND OF CHEST Black 539 E. OHIO CHICAGO 60611 NA TRUE NA NA NA NULL NA Chicago 60623
18 1513609260 Male 1513608480 SUICIDE GUNSHOT WOUND TO CHEST Black 1602 Shermer Road NORTHBROOK 60062 NA TRUE NA 42.1235089 Point c(-87.8293457, 42.1235089) -87.8293457 Northbrook 60062
18 1511490660 Male 1511487600 HOMICIDE GUNSHOT WOUND OF CHEST Black SAUK TRAIL AND ORHARD AVENUE PARK FOREST 60466 NA TRUE NA 41.4759075 Point c(-87.6818341, 41.4759075) -87.6818341 Chicago 60627
10 1500095280 Male 1500092100 HOMICIDE GUNSHOT WOUND OF THE BACK White Latino 3538 E. 97th Street CHICAGO 60617 NA TRUE NA 41.7195776 Point c(-87.5374494, 41.7195776) -87.5374494 Chicago 60617
24 1499618940 Male 1499616240 HOMICIDE MULTIPLE GUNSHOT WOUNDS White Latino 2739 N. LeClaire CHICAGO 60639 NA TRUE NA 41.9308508 Point c(-87.753765, 41.9308508) -87.753765 Harwood Heights 60706
18 1511712840 Male 1511712060 HOMICIDE MULTIPLE GUNSHOT WOUNDS Black 234 N. Lockwood Avenue CHICAGO 60644 NA TRUE NA 41.8853731 Point c(-87.7580285, 41.8853731) -87.7580285 Chicago 60644

A more useful way of displaying the data is summarizing by categories:

2017 Shooting Data Summarized
manner race gender Total
HOMICIDE Black Female 26
HOMICIDE Black Male 301
HOMICIDE Black Latino Male 2
HOMICIDE Latino Male 2
HOMICIDE Other Male 3
HOMICIDE White Female 1
HOMICIDE White Male 6
HOMICIDE White Latino Female 5
HOMICIDE White Latino Male 65
SUICIDE Black Male 13
SUICIDE White Female 4
SUICIDE White Male 11
SUICIDE White Latino Female 1
SUICIDE White Latino Male 4
UNDETERMINED Black Male 2

And by plotting the data.

In short, the data can be filtered, selected and summarized for descriptive statistics, or displayed as a variety of graphs (bar, line, bubble, etc.) With additional time and resources, this data can be combined with the department’s internal data for more analysis and insight. Furthermore, additional time and web formating will allow for interactive graphs that would allow individual users to analyze the data in a variety of ways.

GIS

Because this report is a website, the static GIS maps are interactive. For the purposes of this report,the visualizations are relatively static – It is possible to zoom in on particular neighborhoods and have pop-ups on points – but versions of these maps could be made with user-based selection and filter criteria.

ME Shooting: Incidents by Cook County Commissioner District

Cook County Shooting Incidents 2016 - Today. Source Cook County Medical Examiner’s Office 

## Warning in pal(v): Some values were outside the color scale and will be
## treated as NA

Medical Examiner

A note about the data sets: The ME data was initially downloaded and cleaned before mapped. Their records were missing 16 location entries. In a more formal report, these missing records would be more thoroughly examined. For this proof of concept, they were simply removed from the data set.

These maps are not limited to yearly intervals: Chloropleths can be based on daily, monthly, or quarterly dates. The restrictions of this level of detail is not the amount of work, but where the data will be displayed. This report was written as a webpage and printed to PDF because that level of work is relatively easy. If this report was a standard report, then in addition to being a web page, it would also be printable as a PDF. However, another option would be the creation of a dashboard.

Displaying Reports

Using the data and methods contained in this document, creating a GIS-informed report that runs regularly would be a relatively simple task. Creating a dashboard or app to display the same data would only be marginally more intensive. The most pressing concern about a dashboard/app would be getting permission from County stakeholders to approve the use of an internal web server.

The advantages of a dashboard/app would be the ability to filter the data sets and display them in real time, instead of “hard coding” results like this report. This method would increase collaboration, free up staff time for other analysis, generate less paper, and allow for easy access for all OCJ staff. A note about CFive. They may, or may not, have this capacity built in a future build. This report is not currently contained within the Statement of Work and CFive uses different methods for visualizations. Additionally, to date we have yet to see any of their forms or reports.

A dashboard of the ME’s data can be prototyped within a few weeks. Allyson’s data cannot be displayed this way unless additional steps are taken to protect the identity of court-involved youth.

Recap and Next Steps

The data provided by Allyson can be plotted onto a map of the city or county to reveal counts of shooting incidents. That requirement was met through the creation of this report. Minor tweaks to collection, cleaning, and visualizing need to be completed in order to ensure information integrity, and that process can begin when it is approved. ME office data is also easily accessed and visualized; furthermore, combining both data sets can be done in order to analyze gaps from either source.

The next step for this ask is determining if the Department wants to focus on the development of regular, programmable reports like this draft or developing an app/dashboard. Both require roughly the same amount of work. A dashboard/app will require additional permissions from the Office of the Chief Judge and the Bureau of Technology, but this is the primary barrier to this method.

A prototype of this system can be built in a matter of weeks; however, due to the public nature of this measure, the internally collected data would need to be scrubbed of any identifying information in order to protect the identity of court involved youth. The value of this prototype, beyond a proof of concept, would be the ability to analyze a key metric for the court without having to use Excel, R, or any other data analysis tool.